/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

import Beans.NgonNgu;

/**
 *
 * @author Venus
 */
public class DonViTinh_Model {

    public Vector LayDSDVT(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDVT), MaNN, TenDVT from donvitinh where MaNN = " + langid + " Order by MaDVT desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DonViTinh dvt = new Beans.DonViTinh();
                dvt.setMaDVT(rss.getInt(1));
                dvt.setMaNN(rss.getInt(2));
                dvt.setTenDVT(rss.getString(3));
                v.add(dvt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean KiemTraTenDVT(String dvtname) {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from donvitinh where TenDVT = '" + dvtname + "'";
            rss = stmt.executeQuery(query);
            boolean n = false;
            if (rss.next()) {
                n = true;
            }
            stmt.close();
            rss.close();
            conn.close();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int TaoMa() {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(madvt) from donvitinh";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean AddDVT(int madvt, String dvtname) {

        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into donvitinh values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "(" + madvt + ", " + dsnn[i].getMaNN() + ", '" + dvtname + "'),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector Detail(int madvt) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from donvitinh where MaDVT = " + madvt;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DonViTinh dvt = new Beans.DonViTinh();
                dvt.setMaDVT(rss.getInt(1));
                dvt.setMaNN(rss.getInt(2));
                dvt.setTenDVT(rss.getString(3));
                v.add(dvt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean KiemTraSuaTenDVT(String tendvt, String madvt) {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from donvitinh where TenDVT = '" + tendvt + "' and MaDVT != " + madvt;
            rss = stmt.executeQuery(query);
            boolean n = false;
            if (rss.next()) {
                n = true;
            }
            stmt.close();
            rss.close();
            conn.close();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean Edit(String madvt, String mann, String tendvt) {

        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update donvitinh set TenDVT = '" + tendvt + "' where MaDVT = " + madvt + " and MaNN = " + mann;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SortTenDVT(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDVT), MaNN, TenDVT from donvitinh where MaNN = " + langid + " Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DonViTinh dvt = new Beans.DonViTinh();
                dvt.setMaDVT(rss.getInt(1));
                dvt.setMaNN(rss.getInt(2));
                dvt.setTenDVT(rss.getString(3));
                v.add(dvt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector Search(String tendvt, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDVT), MaNN, TenDVT from donvitinh where MaNN = " + langid + " and TenDVT like '%" + tendvt + "%' Order by MaDVT desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DonViTinh dvt = new Beans.DonViTinh();
                dvt.setMaDVT(rss.getInt(1));
                dvt.setMaNN(rss.getInt(2));
                dvt.setTenDVT(rss.getString(3));
                v.add(dvt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenDVT(int langid, String order, String tendvt) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaDVT), MaNN, TenDVT from donvitinh where MaNN = " + langid + " and TenDVT like '%" + tendvt + "%' Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DonViTinh dvt = new Beans.DonViTinh();
                dvt.setMaDVT(rss.getInt(1));
                dvt.setMaNN(rss.getInt(2));
                dvt.setTenDVT(rss.getString(3));
                v.add(dvt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static String LayTenDVT(int madvt, int langid) {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenDVT from donvitinh where MaNN = " + langid + " and MaDVT = " + madvt + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static Vector LoadDVT(int langid, String ma) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select DVT.MaDVT,MaNN,TenDVT  from donvitinh DVT, dvtmonan MA where MaNN = " + langid + " and DVT.MaDVT = MA.MaDVT and MaMA = " + ma + " Order by MA.MaDVT  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DonViTinh dvt = new Beans.DonViTinh();
                dvt.setMaDVT(rss.getInt(1));
                dvt.setMaNN(rss.getInt(2));
                dvt.setTenDVT(rss.getString(3));
                v.add(dvt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
}
